package com.joysuch.wwyt.risk.repository;

import com.joysuch.wwyt.risk.entity.RiskPointEvaluationItem;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Set;

public interface RiskPointEvaluationItemDao extends JpaRepository<RiskPointEvaluationItem, Long> {
    List<RiskPointEvaluationItem> findByPointEvaluationId(Long id);

    List<RiskPointEvaluationItem> findByAnalyzeWayId(Long analyzeWayId);

    @Modifying
    @Query(value = "update wwyt_risk_point_business_evaluation_item set delete_flag = '1' where id in ?1", nativeQuery = true)
    void deleteAllByIds(List<Long> deleteItemIds);

    @Query(value = "SELECT * FROM wwyt_risk_point_business_evaluation_item  WHERE id IN (SELECT DISTINCT(item_id) FROM wwyt_risk_point_business_evaluation_detail WHERE delete_flag = '0' AND point_evaluation_id = :pointEvaluationId AND method_analyze_type = 2 AND method_id IS NOT NULL AND content IS NOT NULL " +
            "and if(:controlLevelId != '',find_in_set(:controlLevelId,control_level_ids) ,1=1) " +
            "and if(:controlState != '',control_state = :controlState ,1=1) " +
            "and if(:responseDepartId != '',find_in_set(:responseDepartId,response_depart_ids),1=1) " +
            "and if(:responseUserId != '',find_in_set(:responseUserId,response_user_ids),1=1)) AND delete_flag = '0'", nativeQuery = true)
    List<RiskPointEvaluationItem> findByPointControlEvaluationItemAndContion(@Param("pointEvaluationId") Long pointEvaluationId,
                                                                             @Param("controlLevelId") Long controlLevelId,
                                                                             @Param("controlState") Integer controlState,
                                                                             @Param("responseDepartId") Long responseDepartId,
                                                                             @Param("responseUserId") Long responseUserId);

    @Query(value = "SELECT * FROM wwyt_risk_point_business_evaluation_item  WHERE point_evaluation_id = :pointEvaluationId AND delete_flag = '0'", nativeQuery = true)
    List<RiskPointEvaluationItem> findByPointEvaluationItem(@Param("pointEvaluationId") Long pointEvaluationId);


    @Query(value = "select * from wwyt_risk_point_business_evaluation_item where id in (select distinct(item_id) from wwyt_risk_point_business_evaluation_detail " +
            "where delete_flag = '0' and method_analyze_type = 2 and control_state in :controlStateList and point_evaluation_id in :collect and method_id is not null and content is not null " +
            "and if(:controlLevelId != '',find_in_set(:controlLevelId,control_level_ids),1=1) " +
            "and if(:responseDepartId != '',find_in_set(:responseDepartId,response_depart_ids),1=1)) and delete_flag = '0'order by id desc", nativeQuery = true)
    List<RiskPointEvaluationItem> pageControlPointItem(@Param("collect") List<Long> collect,
                                                       @Param("controlLevelId") Long controlLevelId,
                                                       @Param("responseDepartId") Long responseDepartId,
                                                       @Param("controlStateList") List<Integer> controlStateList);


    @Modifying
    @Query(value = "UPDATE `wwyt_risk_point_business_evaluation_item` SET delete_flag = '1' WHERE point_evaluation_id =?1", nativeQuery = true)
    void deleteByPointEvaluationId(Long riskPointEvaluationId);

    @Query(value = "SELECT MIN(risk_level) FROM wwyt_risk_point_business_evaluation_item WHERE delete_flag = '0'AND control_failure_count <1 AND point_analyze_type =1 AND point_evaluation_id = ?1 AND id !=?2 ",nativeQuery = true)
    Integer getOtherControlItemRiskLevel(Long pointEvaluationId,Long id);

    @Query(value = "SELECT MIN(risk_level) FROM wwyt_risk_point_business_evaluation_item WHERE id IN(SELECT stable_item_id FROM wwyt_risk_point_business_evaluation_item WHERE delete_flag = '0' AND point_evaluation_id = ?1 AND control_failure_count >0 AND point_analyze_type =1)",nativeQuery = true)
    Integer getStableMinRiskLevel(Long pointEvaluationId);

    @Query(value = "SELECT MIN(risk_level) FROM wwyt_risk_point_business_evaluation_item WHERE delete_flag = '0'AND control_failure_count <1 AND point_analyze_type =1 AND point_evaluation_id = ?1  ",nativeQuery = true)
    Integer getControlItemRiskLevel(Long pointEvaluationId);

    @Query(value = "SELECT * FROM wwyt_risk_point_business_evaluation_item WHERE delete_flag = '0'AND id IN ?1 ",nativeQuery = true)
    List<RiskPointEvaluationItem> findByIds(Set<Long> keySet);

    @Query(value = "SELECT * FROM wwyt_risk_point_business_evaluation_item WHERE delete_flag = '0'AND risk_point_id IN ?1 ",nativeQuery = true)
    List<RiskPointEvaluationItem> findAllByPointId(List<Long> pointIds);
}
